﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace EA_therapy
{
    /*
    数据库操作类 - 增/删/改/查
    */
    public class UseAccessDb
    {
        static protected OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=dz_database.mdb");
        /*
        方法：增加数据库条目
        形参：SQL语句
        返回值：bool
        */
        static public Boolean AddNote(string sql)
        {

            Boolean tempvalue = false;
            try
            {
                conn.Open();
                OleDbCommand insertcmd = new OleDbCommand(sql, conn);
                insertcmd.ExecuteNonQuery();
                conn.Close();
                tempvalue = true;
            }
            catch (Exception e)
            {
                throw (new Exception("数据库添加出错:" + e.Message));
            }
            return (tempvalue);
        }

        /*
        方法：删除数据库条目
        形参：SQL语句
        返回值：bool
        */
        static public Boolean DelNote(string sql,string list,string key,string value)
        {
            Boolean tempvalue = false;
            try
            {
                conn.Open();
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(sql, conn);
                OleDbCommandBuilder mybuilder = new OleDbCommandBuilder(myDataAdapter);
                DataSet ds = new DataSet(); 
                myDataAdapter.Fill(ds, list);
                foreach (DataRow dr in ds.Tables[list].Rows)
                {
                    if (dr[key].ToString().Equals(value))
                    {
                        dr.Delete();
                    }
                }
                myDataAdapter.Update(ds, list);
                conn.Close();
                tempvalue = true;
                return (tempvalue);
            }
            catch (Exception e)
            {
                throw (new Exception("数据库删除出错:" + e.Message));
            }
        }

        /*
        方法：修改数据库条目
        形参：SQL语句
        返回值：bool
        */
        static public Boolean UpdateNote(string sql)
        {
            Boolean tempvalue = false;
            try
            {
                conn.Open();
                OleDbCommand myCommand = new OleDbCommand(sql, conn);
                myCommand.ExecuteNonQuery(); 
                conn.Close();
                tempvalue = true;
                return (tempvalue);
            }
            catch (Exception e)
            {
                throw (new Exception("数据库更新出错:" + sql + "\r" + e.Message));
            }
        }

        /*
        方法：查询数据库条目
        形参：SQL语句
        返回值：数据库数据
        */
        static public DataTable GetNote(string sql)
        {
            try
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable(); 
                da.Fill(dt);
                conn.Close();
                da.Dispose();
                return dt;
            }
            catch (Exception e)
            {
                throw (new Exception("数据库查询出错:" + sql + "\r" + e.Message));
            }
        }

        //处理数据，在把数据存到数据库前，先屏蔽那些危险字符！
        static private string DealString(string str)
        {
            str = str.Replace("<", "<");
            str = str.Replace(">", ">");
            str = str.Replace("\r", "<br>");
            str = str.Replace("\'", "’");
            str = str.Replace("\x0020", " ");

            return (str);
        }

        //恢复数据：把数据库中的数据，还原成未处理前的样子
        static private string UnDealString(string str)
        {
            str = str.Replace("<", "<");
            str = str.Replace(">", ">");
            str = str.Replace("<br>", "\r");
            str = str.Replace("’", "\'");
            str = str.Replace(" ", "\x0020");

            return (str);
        }
    }
}
